const XLSX = require('xlsx');
const crypto = require('crypto');
const { logger } = require('../utils/logger');
const Coupon = require('../models/Coupon');
const ImportJob = require('../models/ImportJob');

class CouponImportService {
  constructor() {
    this.templateHeaders = [
      'title',
      'description', 
      'discount_type',
      'discount_value',
      'minimum_order_amount',
      'maximum_discount_amount',
      'validity_type',
      'valid_from',
      'valid_until',
      'validity_days',
      'usage_limit_per_user',
      'total_usage_limit',
      'status',
      'remarks',
      'eligibility_rules',
      'inclusion_rules',
      'exclusion_rules',
      'time_windows'
    ];
    
    this.requiredColumns = [
      'title',
      'discount_type',
      'discount_value',
      'validity_type'
    ];
  }

  /**
   * 生成Excel模板文件
   * @returns {Buffer} Excel文件缓冲区
   */
  generateTemplate() {
    try {
      // 创建示例数据
      const sampleData = [
        {
          title: '新用户专享优惠券',
          description: '新注册用户专享S$10优惠',
          discount_type: 'amount',
          discount_value: 10,
          minimum_order_amount: 50,
          maximum_discount_amount: null,
          validity_type: 'absolute',
          valid_from: '2024-01-01',
          valid_until: '2024-12-31',
          validity_days: null,
          usage_limit_per_user: 1,
          total_usage_limit: 1000,
          status: 'active',
          remarks: '仅限新用户使用',
          eligibility_rules: '{"user_type": "new", "min_age": 18}',
          inclusion_rules: '{"categories": ["food", "beverage"]}',
          exclusion_rules: '{"products": ["alcohol"]}',
          time_windows: '{"weekdays": [1,2,3,4,5], "hours": ["09:00-22:00"]}'
        },
        {
          title: '会员专享折扣券',
          description: '会员专享8折优惠',
          discount_type: 'percent',
          discount_value: 20,
          minimum_order_amount: 30,
          maximum_discount_amount: 50,
          validity_type: 'relative',
          valid_from: null,
          valid_until: null,
          validity_days: 30,
          usage_limit_per_user: 3,
          total_usage_limit: null,
          status: 'active',
          remarks: '会员等级Gold以上可用',
          eligibility_rules: '{"membership_level": ["gold", "platinum"]}',
          inclusion_rules: '{}',
          exclusion_rules: '{}',
          time_windows: '{}'
        },
        {
          title: '免费配送券',
          description: '免费配送服务',
          discount_type: 'free',
          discount_value: 0,
          minimum_order_amount: 0,
          maximum_discount_amount: null,
          validity_type: 'absolute',
          valid_from: '2024-06-01',
          valid_until: '2024-06-30',
          validity_days: null,
          usage_limit_per_user: 5,
          total_usage_limit: 500,
          status: 'active',
          remarks: '免费配送，不限金额',
          eligibility_rules: '{}',
          inclusion_rules: '{"service_type": "delivery"}',
          exclusion_rules: '{}',
          time_windows: '{}'
        }
      ];

      // 创建工作簿
      const workbook = XLSX.utils.book_new();
      
      // 创建数据工作表
      const worksheet = XLSX.utils.json_to_sheet(sampleData);
      
      // 设置列宽
      const colWidths = this.templateHeaders.map(() => ({ wch: 20 }));
      worksheet['!cols'] = colWidths;
      
      XLSX.utils.book_append_sheet(workbook, worksheet, '优惠券数据');
      
      // 生成Excel缓冲区
      const buffer = XLSX.write(workbook, { type: 'buffer', bookType: 'xlsx' });
      
      console.log('优惠券模板生成成功');
      return buffer;
    } catch (error) {
      console.error('生成优惠券模板失败:', error);
      throw error;
    }
  }

  /**
   * 解析Excel文件
   * @param {Buffer} fileBuffer - Excel文件缓冲区
   * @returns {Object} 解析结果
   */
  async parseExcelFile(fileBuffer) {
    try {
      const workbook = XLSX.read(fileBuffer, { type: 'buffer' });
      const sheetName = workbook.SheetNames[0];
      const worksheet = workbook.Sheets[sheetName];
      
      // 转换为JSON数据
      const rawData = XLSX.utils.sheet_to_json(worksheet);
      
      console.log(`解析Excel文件成功，共${rawData.length}行数据`);
      
      const validationResults = {
        validRows: [],
        invalidRows: [],
        errors: []
      };
      
      // 逐行验证和处理
      for (let i = 0; i < rawData.length; i++) {
        const rowIndex = i + 2; // Excel行号从2开始（第1行是标题）
        const row = rawData[i];
        
        try {
          const processedRow = await this.processRow(row, rowIndex);
          validationResults.validRows.push(processedRow);
        } catch (error) {
          validationResults.invalidRows.push({
            rowIndex,
            data: row,
            error: error.message
          });
          validationResults.errors.push({
            row: rowIndex,
            message: error.message
          });
        }
      }
      
      logger.info(`数据验证完成: 有效${validationResults.validRows.length}行, 无效${validationResults.invalidRows.length}行`);
      
      return validationResults;
    } catch (error) {
      logger.error('解析Excel文件失败:', error);
      throw error;
    }
  }

  /**
   * 处理单行数据
   * @param {Object} row - 行数据
   * @param {number} rowIndex - 行索引
   * @returns {Object} 处理后的数据
   */
  async processRow(row, rowIndex) {
    const errors = [];
    
    // 基础字段验证
    if (!row.title || row.title.trim().length === 0) {
      errors.push('标题不能为空');
    }
    
    if (!row.discount_type || !['amount', 'percent', 'free'].includes(row.discount_type)) {
      errors.push('优惠类型必须是 amount, percent 或 free');
    }
    
    // 优惠值验证
    const discountValidation = this.validateDiscountValue(row.discount_type, row.discount_value);
    if (!discountValidation.valid) {
      errors.push(discountValidation.error);
    }
    
    // 有效期验证
    if (!row.validity_type || !['absolute', 'relative'].includes(row.validity_type)) {
      errors.push('有效期类型必须是 absolute 或 relative');
    }
    
    if (row.validity_type === 'absolute') {
      if (!row.valid_from || !row.valid_until) {
        errors.push('绝对有效期必须设置开始和结束时间');
      } else {
        const fromDate = new Date(row.valid_from);
        const untilDate = new Date(row.valid_until);
        if (isNaN(fromDate.getTime()) || isNaN(untilDate.getTime())) {
          errors.push('有效期日期格式不正确');
        } else if (fromDate >= untilDate) {
          errors.push('有效期开始时间必须早于结束时间');
        }
      }
    } else if (row.validity_type === 'relative') {
      if (!row.validity_days || row.validity_days <= 0) {
        errors.push('相对有效期必须设置有效天数且大于0');
      }
    }
    
    // JSON字段验证
    const jsonFields = ['eligibility_rules', 'inclusion_rules', 'exclusion_rules', 'time_windows'];
    const processedRow = { ...row };
    
    for (const field of jsonFields) {
      if (row[field]) {
        try {
          if (typeof row[field] === 'string') {
            JSON.parse(row[field]); // 验证JSON格式
            processedRow[field] = row[field];
          } else {
            processedRow[field] = JSON.stringify(row[field]);
          }
        } catch (e) {
          errors.push(`${field} JSON格式不正确`);
        }
      } else {
        processedRow[field] = '{}';
      }
    }
    
    // 数值字段处理
    processedRow.discount_value = Number(row.discount_value) || 0;
    processedRow.minimum_order_amount = Number(row.minimum_order_amount) || 0;
    processedRow.maximum_discount_amount = row.maximum_discount_amount ? Number(row.maximum_discount_amount) : null;
    processedRow.usage_limit_per_user = Number(row.usage_limit_per_user) || 1;
    processedRow.total_usage_limit = row.total_usage_limit ? Number(row.total_usage_limit) : null;
    processedRow.validity_days = row.validity_days ? Number(row.validity_days) : null;
    
    // 状态字段处理
    processedRow.status = row.status || 'active';
    if (!['active', 'inactive', 'draft', 'expired'].includes(processedRow.status)) {
      errors.push('状态必须是 active, inactive, draft 或 expired');
    }
    
    if (errors.length > 0) {
      throw new Error(`第${rowIndex}行: ${errors.join(', ')}`);
    }
    
    return processedRow;
  }

  /**
   * 验证优惠值
   * @param {string} discountType - 优惠类型
   * @param {number} discountValue - 优惠值
   * @returns {Object} 验证结果
   */
  validateDiscountValue(discountType, discountValue) {
    const value = Number(discountValue);
    
    switch (discountType) {
      case 'amount':
        if (isNaN(value) || value <= 0) {
          return { valid: false, error: '金额券优惠值必须大于0' };
        }
        break;
      case 'percent':
        if (isNaN(value) || value <= 0 || value > 100) {
          return { valid: false, error: '折扣券优惠值必须在0-100之间' };
        }
        break;
      case 'free':
        if (value !== 0) {
          return { valid: false, error: '免费券优惠值必须为0' };
        }
        break;
      default:
        return { valid: false, error: '未知的优惠类型' };
    }
    
    return { valid: true };
  }

  /**
   * 批量导入优惠券
   * @param {Array} validRows - 有效的行数据
   * @param {number} createdBy - 创建者ID
   * @returns {Object} 导入结果
   */
  async importCoupons(validRows, createdBy = null) {
    const results = {
      success: [],
      failed: [],
      total: validRows.length
    };
    
    for (let i = 0; i < validRows.length; i++) {
      const row = validRows[i];
      
      try {
        const coupon = await Coupon.create(row, createdBy);
        results.success.push({
          title: row.title,
          coupon_id: coupon.coupon_id
        });
        logger.info(`优惠券导入成功: ${row.title}`);
      } catch (error) {
        results.failed.push({
          title: row.title,
          error: error.message
        });
        logger.error(`优惠券导入失败: ${row.title}`, error);
      }
    }
    
    logger.info(`批量导入完成: 成功${results.success.length}个, 失败${results.failed.length}个`);
    return results;
  }

  /**
   * 生成错误报告CSV
   * @param {Array} errors - 错误列表
   * @returns {string} CSV内容
   */
  generateErrorReport(errors) {
    if (errors.length === 0) {
      return '';
    }
    
    const csvHeader = '行号,错误信息\n';
    const csvRows = errors.map(error => `${error.row},"${error.message}"`).join('\n');
    
    return csvHeader + csvRows;
  }

  /**
   * 校验单行数据
   */
  async validateSingleRow(row, titleSet) {
    const result = {
      isValid: true,
      errors: [],
      warnings: [],
      data: null
    };

    try {
      // 检查必填字段
      if (!row.title || !row.title.toString().trim()) {
        result.errors.push('Coupon Title不能为空');
        result.isValid = false;
      }
      
      if (!row.discount || !row.discount.toString().trim()) {
        result.errors.push('优惠/Discount不能为空');
        result.isValid = false;
      }

      // 检查文件内重复
      const title = row.title.toString().trim();
      if (titleSet.has(title)) {
        result.errors.push('同一文件内Coupon Title重复');
        result.isValid = false;
      } else {
        titleSet.add(title);
      }

      if (!result.isValid) {
        return result;
      }

      // 解析和规范化数据
      const normalizedData = await this.normalizeRowData(row);
      result.data = normalizedData;

      // 业务规则校验
      const businessValidation = this.validateBusinessRules(normalizedData);
      if (!businessValidation.isValid) {
        result.errors.push(...businessValidation.errors);
        result.isValid = false;
      }
      
      result.warnings.push(...businessValidation.warnings);

    } catch (error) {
      result.errors.push(`数据处理错误: ${error.message}`);
      result.isValid = false;
    }

    return result;
  }

  /**
   * 规范化行数据
   */
  async normalizeRowData(row) {
    const data = {
      title: row.title.toString().trim(),
      description: row.description ? row.description.toString().trim() : '',
      remarks: row.remarks ? row.remarks.toString().trim() : '',
      _rowIndex: row._rowIndex
    };

    // 解析优惠信息
    const discountInfo = this.parseDiscount(row.discount.toString().trim());
    data.discount_type = discountInfo.type;
    data.discount_value = discountInfo.value;

    // 解析有效期
    const validityInfo = this.parseValidity(row.validity);
    data.validity_type = validityInfo.type;
    data.valid_from = validityInfo.valid_from;
    data.valid_until = validityInfo.valid_until;
    data.relative_days = validityInfo.relative_days;

    // 解析各种规则
    data.eligibility_rules = this.parseEligibilityRules(row.eligibility_conditions);
    data.inclusion_rules = this.parseApplicableItems(row.applicable_items, 'include');
    data.exclusion_rules = this.parseApplicableItems(row.applicable_items, 'exclude');
    data.time_windows = this.parseTimeWindows(row.applicable_time);
    data.usage_quota = this.parseUsageLimit(row.usage_limit);

    // 生成规则哈希
    data.eligibility_hash = this.generateRulesHash({
      eligibility_rules: data.eligibility_rules,
      inclusion_rules: data.inclusion_rules,
      exclusion_rules: data.exclusion_rules,
      time_windows: data.time_windows,
      usage_quota: data.usage_quota
    });

    return data;
  }

  /**
   * 解析优惠信息
   */
  parseDiscount(discountStr) {
    const str = discountStr.toLowerCase().trim();
    
    // Free 类型
    if (str === 'free' || str === '免费') {
      return { type: 'free', value: null };
    }
    
    // 金额券：S$X off 或 $X off
    const amountMatch = str.match(/s?\$([0-9.]+)\s*off/i) || str.match(/([0-9.]+)元/);
    if (amountMatch) {
      const value = parseFloat(amountMatch[1]);
      if (isNaN(value) || value < 0) {
        throw new Error('金额券数值必须为非负数');
      }
      return { type: 'amount', value };
    }
    
    // 折扣券：X% off 或 X折
    const percentMatch = str.match(/([0-9.]+)%\s*off/i) || str.match(/([0-9.]+)折/);
    if (percentMatch) {
      let value = parseFloat(percentMatch[1]);
      
      // 处理中文"折"的情况（如8折 = 80%）
      if (str.includes('折') && value <= 10) {
        value = value * 10;
      }
      
      if (isNaN(value) || value < 0 || value > 100) {
        throw new Error('折扣百分比必须在0-100之间');
      }
      return { type: 'percent', value };
    }
    
    throw new Error(`无法解析优惠格式: ${discountStr}`);
  }

  /**
   * 解析有效期
   */
  parseValidity(validityStr) {
    if (!validityStr || !validityStr.toString().trim()) {
      return {
        type: 'absolute',
        valid_from: null,
        valid_until: null,
        relative_days: null
      };
    }

    const str = validityStr.toString().trim();
    
    // 相对时间："注册后30天" 或 "30 days after registration"
    const relativeDaysMatch = str.match(/(\d+)\s*天|after\s+(\d+)\s*days/i);
    if (relativeDaysMatch) {
      const days = parseInt(relativeDaysMatch[1] || relativeDaysMatch[2]);
      return {
        type: 'relative',
        valid_from: null,
        valid_until: null,
        relative_days: days
      };
    }
    
    // 绝对时间："2024-01-01 to 2024-12-31" 或 "2024-01-01~2024-12-31"
    const absoluteMatch = str.match(/(\d{4}-\d{2}-\d{2})\s*(?:to|~|至)\s*(\d{4}-\d{2}-\d{2})/i);
    if (absoluteMatch) {
      const startDate = new Date(absoluteMatch[1]);
      const endDate = new Date(absoluteMatch[2]);
      
      if (isNaN(startDate.getTime()) || isNaN(endDate.getTime())) {
        throw new Error('日期格式无效，请使用YYYY-MM-DD格式');
      }
      
      if (startDate > endDate) {
        throw new Error('开始日期不能晚于结束日期');
      }
      
      return {
        type: 'absolute',
        valid_from: startDate,
        valid_until: endDate,
        relative_days: null
      };
    }
    
    throw new Error(`无法解析有效期格式: ${validityStr}`);
  }

  /**
   * 解析解锁条件
   */
  parseEligibilityRules(conditionsStr) {
    if (!conditionsStr || !conditionsStr.toString().trim()) {
      return {};
    }

    const rules = {};
    const str = conditionsStr.toString().trim();
    
    // 会员等级
    const levelMatch = str.match(/(?:会员等级|member\s+level)[:：]?\s*([^,;，；]+)/i);
    if (levelMatch) {
      rules.member_level = levelMatch[1].trim();
    }
    
    // 注册完成度
    const registrationMatch = str.match(/(?:注册完成度|registration)[:：]?\s*(\d+)%/i);
    if (registrationMatch) {
      rules.registration_completion = parseInt(registrationMatch[1]);
    }
    
    // 连续打卡
    const checkinMatch = str.match(/(?:连续打卡|consecutive\s+checkin)[:：]?\s*(\d+)\s*(?:天|days)/i);
    if (checkinMatch) {
      rules.consecutive_checkin_days = parseInt(checkinMatch[1]);
    }
    
    return rules;
  }

  /**
   * 解析适用范围
   */
  parseApplicableItems(itemsStr, type = 'include') {
    if (!itemsStr || !itemsStr.toString().trim()) {
      return {};
    }

    const rules = {};
    const str = itemsStr.toString().trim();
    
    // 检查是包含还是排除
    const isExclude = /排除|exclude|except/i.test(str);
    const isInclude = /仅限|only|include/i.test(str);
    
    if (type === 'exclude' && !isExclude) {
      return {};
    }
    if (type === 'include' && !isInclude) {
      return {};
    }
    
    // 解析分类
    const categoryMatch = str.match(/(?:分类|category)[:：]?\s*([^,;，；酒水]+)/i);
    if (categoryMatch) {
      rules.categories = categoryMatch[1].split(/[,，]/).map(c => c.trim()).filter(c => c);
    }
    
    // 解析商品
    const itemMatch = str.match(/(?:商品|item|product)[:：]?\s*([^,;，；酒水]+)/i);
    if (itemMatch) {
      rules.items = itemMatch[1].split(/[,，]/).map(i => i.trim()).filter(i => i);
    }
    
    // 酒水相关
    if (/酒水|alcohol|beverage/i.test(str)) {
      rules.include_alcohol = !isExclude;
    }
    
    return rules;
  }

  /**
   * 解析时间窗口
   */
  parseTimeWindows(timeStr) {
    if (!timeStr || !timeStr.toString().trim()) {
      return [];
    }

    const windows = [];
    const str = timeStr.toString().trim();
    
    // 解析格式："周一-周五 09:00-18:00" 或 "Mon-Fri 09:00-18:00"
    const windowMatches = str.match(/([^,;，；]+)/g) || [];
    
    for (const windowStr of windowMatches) {
      const window = this.parseTimeWindow(windowStr.trim());
      if (window) {
        windows.push(window);
      }
    }
    
    return windows;
  }

  /**
   * 解析单个时间窗口
   */
  parseTimeWindow(windowStr) {
    // 周几映射
    const dayMapping = {
      '周一': 1, '周二': 2, '周三': 3, '周四': 4, '周五': 5, '周六': 6, '周日': 0,
      'monday': 1, 'tuesday': 2, 'wednesday': 3, 'thursday': 4, 'friday': 5, 'saturday': 6, 'sunday': 0,
      'mon': 1, 'tue': 2, 'wed': 3, 'thu': 4, 'fri': 5, 'sat': 6, 'sun': 0
    };
    
    // 解析："周一-周五 09:00-18:00"
    const match = windowStr.match(/([^\s]+)\s+(\d{2}:\d{2})-(\d{2}:\d{2})/i);
    if (!match) {
      return null;
    }
    
    const [, dayRange, startTime, endTime] = match;
    const days = [];
    
    // 解析日期范围
    if (dayRange.includes('-')) {
      const [startDay, endDay] = dayRange.split('-');
      const startDayNum = dayMapping[startDay.toLowerCase()];
      const endDayNum = dayMapping[endDay.toLowerCase()];
      
      if (startDayNum !== undefined && endDayNum !== undefined) {
        for (let i = startDayNum; i <= endDayNum; i++) {
          days.push(i);
        }
      }
    } else {
      const dayNum = dayMapping[dayRange.toLowerCase()];
      if (dayNum !== undefined) {
        days.push(dayNum);
      }
    }
    
    if (days.length === 0) {
      return null;
    }
    
    return {
      days,
      start_time: startTime,
      end_time: endTime
    };
  }

  /**
   * 解析使用频率
   */
  parseUsageLimit(limitStr) {
    if (!limitStr || !limitStr.toString().trim()) {
      return {};
    }

    const quota = {};
    const str = limitStr.toString().trim();
    
    // 解析每用户限制："每用户1次" 或 "1 per user"
    const perUserMatch = str.match(/(?:每用户|per\s+user)[:：]?\s*(\d+)\s*(?:次|times?)/i);
    if (perUserMatch) {
      quota.per_user = parseInt(perUserMatch[1]);
    }
    
    // 解析每日限制
    const perDayMatch = str.match(/(?:每日|per\s+day)[:：]?\s*(\d+)\s*(?:次|times?)/i);
    if (perDayMatch) {
      quota.per_day = parseInt(perDayMatch[1]);
    }
    
    // 解析每周限制
    const perWeekMatch = str.match(/(?:每周|per\s+week)[:：]?\s*(\d+)\s*(?:次|times?)/i);
    if (perWeekMatch) {
      quota.per_week = parseInt(perWeekMatch[1]);
    }
    
    // 解析每年限制
    const perYearMatch = str.match(/(?:每年|per\s+year)[:：]?\s*(\d+)\s*(?:次|times?)/i);
    if (perYearMatch) {
      quota.per_year = parseInt(perYearMatch[1]);
    }
    
    return quota;
  }

  /**
   * 生成规则哈希
   */
  generateRulesHash(rules) {
    const rulesStr = JSON.stringify(rules, Object.keys(rules).sort());
    return crypto.createHash('sha256').update(rulesStr).digest('hex');
  }

  /**
   * 业务规则校验
   */
  validateBusinessRules(data) {
    const result = {
      isValid: true,
      errors: [],
      warnings: []
    };

    // 校验优惠值
    if (data.discount_type === 'free' && data.discount_value !== null) {
      result.errors.push('免费券的优惠数值必须为空');
      result.isValid = false;
    }
    
    if (data.discount_type !== 'free' && (data.discount_value === null || data.discount_value <= 0)) {
      result.errors.push('金额券和折扣券的优惠数值必须大于0');
      result.isValid = false;
    }
    
    if (data.discount_type === 'percent' && data.discount_value > 100) {
      result.errors.push('折扣百分比不能超过100%');
      result.isValid = false;
    }

    // 校验时间窗口
    if (data.time_windows && data.time_windows.length > 0) {
      for (const window of data.time_windows) {
        if (!this.isValidTimeWindow(window)) {
          result.errors.push(`时间窗口格式无效: ${JSON.stringify(window)}`);
          result.isValid = false;
        }
      }
    }

    // 校验适用范围
    if ((!data.inclusion_rules || Object.keys(data.inclusion_rules).length === 0) &&
        (!data.exclusion_rules || Object.keys(data.exclusion_rules).length === 0)) {
      result.warnings.push('未指定适用范围，将适用于所有商品');
    }

    return result;
  }

  /**
   * 校验时间窗口格式
   */
  isValidTimeWindow(window) {
    if (!window.days || !Array.isArray(window.days) || window.days.length === 0) {
      return false;
    }
    
    if (!window.start_time || !window.end_time) {
      return false;
    }
    
    // 校验时间格式
    const timeRegex = /^\d{2}:\d{2}$/;
    if (!timeRegex.test(window.start_time) || !timeRegex.test(window.end_time)) {
      return false;
    }
    
    // 校验星期几
    for (const day of window.days) {
      if (!Number.isInteger(day) || day < 0 || day > 6) {
        return false;
      }
    }
    
    return true;
  }

  /**
   * 检查数据库重复
   */
  async checkDatabaseDuplicates(processedData, results) {
    for (const data of processedData) {
      try {
        const existing = await Coupon.findOne({
          where: {
            title: data.title,
            eligibility_hash: data.eligibility_hash
          }
        });
        
        if (existing) {
          data._isDuplicate = true;
          data._existingId = existing.id;
          results.statistics.duplicates++;
          
          results.warnings.push({
            row: data._rowIndex,
            title: data.title,
            warnings: [`礼券已存在(ID: ${existing.id})，将更新现有记录`]
          });
        }
      } catch (error) {
        console.error('检查重复时出错:', error);
      }
    }
  }

  /**
   * 创建导入任务
   */
  async createImportJob(validationResults, userId) {
    const job = await ImportJob.create({
      import_type: 'coupon_import',
      admin_id: userId,
      file_name: 'coupon_import.xlsx',
      file_path: null,
      file_size: 0
    });

    // 更新任务状态和验证结果
    await job.updateStatus('validated', {
      total_rows: validationResults.statistics.total,
      valid_rows: validationResults.statistics.valid,
      error_rows: validationResults.statistics.invalid,
      warning_rows: validationResults.warnings.length,
      validation_errors: validationResults.errors,
      dry_run_result: {
        validData: validationResults.validData,
        warnings: validationResults.warnings,
        statistics: validationResults.statistics
      }
    });

    return job;
  }

  /**
   * 提交导入任务
   */
  async commitImportJob(jobId, userId) {
    const job = await ImportJob.findByJobId(jobId);
    if (!job) {
      throw new Error('导入任务不存在');
    }
    
    if (job.status !== 'validated') {
      throw new Error('任务状态无效，无法提交');
    }

    const { validData } = job.dry_run_result;
    const results = {
      created: 0,
      updated: 0,
      errors: []
    };

    // 开始事务
    const transaction = await Coupon.sequelize.transaction();
    
    try {
      // 更新任务状态为处理中
      await job.updateStatus('processing');

      for (const data of validData) {
        try {
          if (data._isDuplicate) {
            // 更新现有记录
            await Coupon.update({
              description: data.description,
              remarks: data.remarks,
              discount_type: data.discount_type,
              discount_value: data.discount_value,
              validity_type: data.validity_type,
              valid_from: data.valid_from,
              valid_until: data.valid_until,
              relative_days: data.relative_days,
              eligibility_rules: data.eligibility_rules,
              inclusion_rules: data.inclusion_rules,
              exclusion_rules: data.exclusion_rules,
              time_windows: data.time_windows,
              usage_quota: data.usage_quota,
              updated_by: userId
            }, {
              where: { id: data._existingId },
              transaction
            });
            results.updated++;
          } else {
            // 创建新记录
            await Coupon.create({
              ...data,
              created_by: userId,
              updated_by: userId
            }, { transaction });
            results.created++;
          }
        } catch (error) {
          results.errors.push({
            row: data._rowIndex,
            title: data.title,
            error: error.message
          });
        }
      }

      // 更新任务状态为完成
      await job.updateStatus('completed', {
        new_records: results.created,
        updated_records: results.updated
      });

      await transaction.commit();
      return results;
      
    } catch (error) {
      await transaction.rollback();
      
      await job.updateStatus('failed', {
        error_message: error.message
      });
      
      throw error;
    }
  }

  /**
   * 验证和规范化数据
   * @param {Array} rawData - 原始数据
   * @returns {Object} 验证结果
   */
  async validateAndNormalizeData(rawData) {
    const results = {
      totalRows: rawData.length,
      validRows: 0,
      errorRows: 0,
      warningRows: 0,
      errors: [],
      warnings: [],
      validData: []
    };

    const titleSet = new Set();

    for (let i = 0; i < rawData.length; i++) {
      const rowIndex = i + 2; // Excel行号从2开始
      const row = rawData[i];

      try {
        const validation = await this.validateSingleRow(row, titleSet);
        
        if (validation.isValid) {
          results.validRows++;
          results.validData.push(validation.data);
          
          if (validation.warnings && validation.warnings.length > 0) {
            results.warningRows++;
            results.warnings.push({
              row: rowIndex,
              title: row.title,
              warnings: validation.warnings
            });
          }
        } else {
          results.errorRows++;
          results.errors.push({
            row: rowIndex,
            title: row.title || '',
            errors: validation.errors
          });
        }
      } catch (error) {
        results.errorRows++;
        results.errors.push({
          row: rowIndex,
          title: row.title || '',
          errors: [error.message]
        });
      }
    }

    logger.info(`数据验证完成: 总计${results.totalRows}行, 有效${results.validRows}行, 错误${results.errorRows}行, 警告${results.warningRows}行`);
    
    return results;
  }
}

module.exports = CouponImportService;